import pandas as pd
import numpy as np


def calculate_section_area(depth_data):
    """计算水道断面面积（梯形面积累加）"""
    if depth_data.empty:
        return 0.0

    # 排序并补全首尾0点
    depth_data = depth_data.sort_values('起点距离(m)').reset_index(drop=True)
    if depth_data.iloc[0]['起点距离(m)'] != 0:
        depth_data = pd.concat([pd.DataFrame({'起点距离(m)': [0], '水深(m)': [0]}), depth_data], ignore_index=True)
    if depth_data.iloc[-1]['起点距离(m)'] != 0:
        depth_data = pd.concat([depth_data, pd.DataFrame({'起点距离(m)': [0], '水深(m)': [0]})], ignore_index=True)

    total_area = 0.0
    for i in range(1, len(depth_data)):
        distance_diff = depth_data.iloc[i]['起点距离(m)'] - depth_data.iloc[i - 1]['起点距离(m)']
        avg_depth = (depth_data.iloc[i - 1]['水深(m)'] + depth_data.iloc[i]['水深(m)']) / 2
        total_area += distance_diff * avg_depth
    return total_area


def calculate_avg_velocity(velocity_data, section_area):
    """计算平均水流速度（加权平均，修复NaN问题）"""
    if section_area == 0:
        return 0.0  # 断面面积为0时无意义

    # 过滤流速缺失的行（保留水深可能缺失的行用于插值）
    velocity_data = velocity_data.dropna(subset=['测点水流速(m/s)']).copy()
    if velocity_data.empty:
        return 0.0  # 无有效流速数据

    # 处理测点水深缺失：使用前后点插值（关键修复）
    velocity_data['测点水深(m)'] = velocity_data['测点水深(m)'].interpolate(method='linear')

    # 排序并补全边界点（使用实际最小/最大距离外扩1米）
    min_dist = velocity_data['起点距离(m)'].min()
    max_dist = velocity_data['起点距离(m)'].max()
    boundary_points = pd.DataFrame({
        '起点距离(m)': [min_dist - 1, max_dist + 1],
        '测点水深(m)': [0, 0],
        '测点水流速(m/s)': [0, 0]
    })
    velocity_data = pd.concat([boundary_points, velocity_data], ignore_index=True) \
        .sort_values('起点距离(m)') \
        .reset_index(drop=True)

    weighted_sum = 0.0
    for j in range(1, len(velocity_data) - 1):
        # 计算小区域宽度（左右相邻点间距的一半）
        left_dist = velocity_data.iloc[j - 1]['起点距离(m)']
        right_dist = velocity_data.iloc[j + 1]['起点距离(m)']
        width = (right_dist - left_dist) / 2

        # 确保水深非空（插值后应该已有值）
        height = velocity_data.iloc[j]['测点水深(m)']
        if pd.isna(height):  # 备用方案：使用上下点平均
            height = (velocity_data.iloc[j - 1]['测点水深(m)'] + velocity_data.iloc[j + 1]['测点水深(m)']) / 2

        area = width * height  # 小区域面积
        weighted_sum += velocity_data.iloc[j]['测点水流速(m/s)'] * area  # 流速加权

    return weighted_sum / section_area if section_area != 0 else 0.0


def process_monitoring_data(file_path):
    """主处理函数：读取数据并计算结果"""
    df = pd.read_excel(file_path, header=0)

    # 按日期分组（处理连续日期块）
    date_marks = df['日期'].notna()
    group_indices = np.where(date_marks)[0].tolist() + [len(df)]
    date_groups = []
    for i in range(len(group_indices) - 1):
        start = group_indices[i]
        end = group_indices[i + 1]
        group_df = df.iloc[start:end].copy()
        group_date = group_df['日期'].dropna().iloc[0]
        date_groups.append((group_date, group_df))

    target_dates = {
        '2018-04-04': '2018.4.4',
        '2019-04-17': '2019.4.17',
        '2020-04-17': '2020.4.17',
        '2020-07-10': '2020.7.10',
        '2020-07-11': '2020.7.11',
        '2021-07-09': '2021.7.9'
    }

    results = []
    for raw_date, group in date_groups:
        try:
            std_date = pd.to_datetime(raw_date).strftime('%Y-%m-%d')
        except:
            continue

        if std_date not in target_dates:
            continue

        # 提取测深数据（水深计算断面面积）
        depth_data = group[['起点距离(m)', '水深(m)']].dropna(subset=['水深(m)'])
        # 提取测速数据（保留水深缺失行用于后续插值）
        velocity_data = group[['起点距离(m)', '测点水深(m)', '测点水流速(m/s)']]

        section_area = calculate_section_area(depth_data)
        avg_velocity = calculate_avg_velocity(velocity_data, section_area)

        results.append({
            '监测日': target_dates[std_date],
            '断面面积(m²)': round(section_area, 3),
            '平均水速(m/s)': round(avg_velocity, 4)
        })

    result_order = ['2018.4.4', '2019.4.17', '2020.4.17', '2020.7.10', '2020.7.11', '2021.7.9']
    results = sorted(results, key=lambda x: result_order.index(x['监测日']))
    pass
    return results


if __name__ == "__main__":
    excel_path = "附件3.xlsx"
    table7 = process_monitoring_data(excel_path)

    print("表7. 各监测日水道断面面积、平均水速")
    print(f"{'监测日':<10} | {'断面面积(m²)':<12} | {'平均水速(m/s)':<12}")
    print("-" * 40)
    for row in table7:
        # 处理可能的计算误差（如极小值显示为0）
        water_speed = row['平均水速(m/s)'] if abs(row['平均水速(m/s)']) > 1e-6 else 0.0
        print(f"{row['监测日']:<10} | {row['断面面积(m²)']:<12.3f} | {water_speed:<12.4f}")
